使用 VLOOKUP、INDEX 或 MATCH 尋找值 |
您所在的位置:网站首页 › ios block修饰 › 使用 VLOOKUP、INDEX 或 MATCH 尋找值 |
提示: 請嘗試使用新的 XLOOKUP 和 XMATCH 函數,這是本文所述改良版本的函數。 這些新函數會朝任何方向工作,並預設會返回完全符合專案,因此使用起來比其前置函數更方便。 假設您有一份辦公室位置號碼清單,而且必須知道哪個員工在每個辦公室。 試算表非常龐大,因此您可能會認為這是一項充滿挑戰的工作。 使用查找函數其實相當簡單。 VLOOKUP和HLOOKUP函數以及INDEX和MATCH是 Excel 中一些最實用的函數。 附註: Excel 中已不再提供查找精靈功能。 以下是如何使用 VLOOKUP 的範例。 =VLOOKUP(B2,C2:E7,3,TRUE) 在此範例中,B2 是第一個引數 ,即函數必須運作的資料元素。 對於 VLOOKUP,此第一個引數是您想要尋找的值。 此引數可以是儲存格參照或固定值,例如 "smith" 或 21,000。 第二個引數是儲存格範圍 C2-:E7,用於搜尋您想要尋找的值。 第三個引數是儲存格範圍中的欄,其中包含您尋找的值。 第四個引數為選用。 輸入 TRUE 或 FALSE。 如果您輸入 TRUE,或將引數留白,函數會傳回您在第一個引數中指定的大約符合值。 如果您輸入 FALSE,則函數會符合第一個引數提供的值。 換句話說,將第四個引數留白或輸入 TRUE,會讓您更有彈性。 此範例顯示函數如何運作。 當您在儲存格 B2 (第一個引數) 中輸入值時,VLOOKUP 會搜尋範圍 C2:E7 (第 2 個引數) 中的儲存格,並會從範圍的第三欄 E (第三個引數) 中,將最接近的大約符合結果。
第四個引數是空白的,因此函數會返回大約符合的值。 如果未傳回,您必須輸入欄 C 或 D 的其中一個值以取得結果。 當您熟悉 VLOOKUP 時,HLOOKUP 函數同樣容易使用。 您輸入相同的引數,但它會以列搜尋,而不是以欄搜尋。 使用 INDEX 和 MATCH 而非 VLOOKUP使用 VLOOKUP 有一些限制,VLOOKUP 函數只能從左至右尋找值。 這表示包含您尋找之值的欄應一直位於包含退貨值的欄左側。 如果您的試算表不是以這種方式建立,請不要使用 VLOOKUP。 請改為使用 INDEX 和 MATCH 函數的組合。 此範例顯示一份小型清單,其中包含我們所要搜尋的值 (芝加哥),但這並非位於最左邊的欄。 因此,我們無法使用 VLOOKUP。 我們將改用 MATCH 函數在範圍 B1:B11 中尋找「芝加哥」。 我們在列 4 中找到該值。 接著,INDEX 函數會將該值作為查閱引數,然後在第 4 欄 (欄 D) 中尋找芝加哥的人口。 所使用的公式顯示於儲存格 A14 中。 ![]() 有關使用 INDEX 和 MATCH 而非 VLOOKUP 的更多 範例,請參閱 Microsoft MVP HTTPs://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Bill Jelen 所寫的文章。 小試身手如果您想要先嘗試使用尋找函數,然後再嘗試自己的資料,以下是一些範例資料。 VLOOKUP 工作範例將下列資料複製到空白試算表中。 提示: 將資料貼到 Excel 之前,請設定欄 A 到 C 的欄寬為 250圖元,然後按一下 [自動換行 (索引卷) 。 密度 黏度 溫度 0.457 3.55 500 0.525 3.25 400 0.606 2.93 300 0.675 2.75 250 0.746 2.57 200 0.835 2.38 150 0.946 2.17 100 1.09 1.95 50 1.29 1.71 0 公式 描述 結果 =VLOOKUP(1,A2:C10,2) 在 A 欄中尋找大約符合 1 的值,在 A 欄中找到一個小於或等於 1 的最大值 (0.946),再傳回 B 欄中同一列的值。 2.17 =VLOOKUP(1,A2:C10,3,TRUE) 在 A 欄中尋找大約符合 1 的值,在 A 欄中找到一個小於或等於 1 的最大值 (0.946),再傳回 C 欄中同一列的值。 100 =VLOOKUP(0.7,A2:C10,3,FALSE) 在 A 欄中尋找完全符合 0.7 的值。因為 A 欄中沒有完全符合的值,因此傳回錯誤。 #N/A =VLOOKUP(0.1,A2:C10,2,TRUE) 在 A 欄中尋找大約符合 0.1 的值。因為 0.1 小於 A 欄中的最小值,因此傳回錯誤。 #N/A =VLOOKUP(2,A2:C10,2,TRUE) 在 A 欄中尋找大約符合 2 的值,在 A 欄中找到一個小於或等於 2 的最大值 (1.29),再傳回 B 欄中同一列的值。 1.71 HLOOKUP 範例複製這個資料表中的所有儲存格,並貼到 Excel 空白工作表的 A1 儲存格中。 提示: 將資料貼到 Excel 之前,請設定欄 A 到 C 的欄寬為 250圖元,然後按一下 [自動換行 (索引卷) 。 輪軸 軸承 螺栓 4 4 9 5 7 10 6 8 11 公式 描述 結果 =HLOOKUP("輪軸", A1:C4, 2, TRUE) 在列 1 中查詢 "輪軸",從同一欄 (欄 A) 的列 2 傳回值。 4 =HLOOKUP("軸承", A1:C4, 3, FALSE) 在列 1 中查詢 "軸承",從同一欄 (欄 B) 的列 3 傳回值。 7 =HLOOKUP("B", A1:C4, 3, TRUE) 在列 1 中查詢 "B",從同一欄的列 3 傳回值。 由於找不到與 "B" 完全相符的項目,因此使用列 1 中小於 "B" 的最大值:欄 A 中的 "輪軸"。 5 =HLOOKUP("螺栓", A1:C4, 4) 在列 1 中查詢 "螺栓",從同一欄 (欄 C) 的列 4 傳回值。 11 =HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE) 在三列的常數陣列中查詢數字 3,並從同一欄 (在此案例中為第三欄) 的列 2 傳回值。 常數陣列中有三列的值,每一列均以分號 (;) 區隔。 由於在列 2 找到 "c",而在同一欄是 3,因此會傳回 "c"。 c INDEX 和 MATCH 範例最後一個範例同時使用 INDEX 和 MATCH 函數,以針對五個縣/市分別退回最早的發票號碼及其對應的日期。 因為日期會以數值的形式傳回,所以我們使用 TEXT 函數將其格式設定為日期。 實際上,INDEX 函數會使用 MATCH 函數的結果作為引數。 INDEX 和 MATCH 函數的組合在每個公式中會有兩次的運用——首先用於傳回發票編號,再用於傳回日期。 複製這個資料表中的所有儲存格,並貼到 Excel 空白工作表的 A1 儲存格中。 提示: 將資料貼到 Excel 之前,將欄 A 到 D 的欄寬設為 250 圖元,然後按一下 [自動換列 (索引卷) 。 發票 城市 發票日期 最早發票 (依城市) 與日期 3115 亞特蘭大 4/7/12 ="亞特蘭大 = "&INDEX($A$2:$C$33,MATCH("亞特蘭大",$B$2:$B$33,0),1)& ", 發票日期: " & TEXT(INDEX($A$2:$C$33,MATCH("亞特蘭大",$B$2:$B$33,0),3),"m/d/yy") 3137 亞特蘭大 4/9/12 ="奧斯丁 = "&INDEX($A$2:$C$33,MATCH("奧斯丁",$B$2:$B$33,0),1)& ", 發票日期: " & TEXT(INDEX($A$2:$C$33,MATCH("奧斯丁",$B$2:$B$33,0),3),"m/d/yy") 3154 亞特蘭大 4/11/12 ="達拉斯 = "&INDEX($A$2:$C$33,MATCH("達拉斯",$B$2:$B$33,0),1)& ", 發票日期: " & TEXT(INDEX($A$2:$C$33,MATCH("達拉斯",$B$2:$B$33,0),3),"m/d/yy") 3191 亞特蘭大 4/21/12 ="紐奧良 = "&INDEX($A$2:$C$33,MATCH("紐奧良",$B$2:$B$33,0),1)& ", 發票日期: " & TEXT(INDEX($A$2:$C$33,MATCH("紐奧良",$B$2:$B$33,0),3),"m/d/yy") 3293 亞特蘭大 4/25/12 ="坦帕 = "&INDEX($A$2:$C$33,MATCH("坦帕",$B$2:$B$33,0),1)& ", 發票日期: " & TEXT(INDEX($A$2:$C$33,MATCH("坦帕",$B$2:$B$33,0),3),"m/d/yy") 3331 亞特蘭大 4/27/12 3350 亞特蘭大 4/28/12 3390 亞特蘭大 5/1/12 3441 亞特蘭大 5/2/12 3517 亞特蘭大 5/8/12 3124 奧斯丁 4/9/12 3155 奧斯丁 4/11/12 3177 奧斯丁 4/19/12 3357 奧斯丁 4/28/12 3492 奧斯丁 5/6/12 3316 達拉斯 4/25/12 3346 達拉斯 4/28/12 3372 達拉斯 5/1/12 3414 達拉斯 5/1/12 3451 達拉斯 5/2/12 3467 達拉斯 5/2/12 3474 達拉斯 5/4/12 3490 達拉斯 5/5/12 3503 達拉斯 5/8/12 3151 紐奧良 4/9/12 3438 紐奧良 5/2/12 3471 紐奧良 5/4/12 3160 坦帕 4/18/12 3328 坦帕 4/26/12 3368 坦帕 4/29/12 3420 坦帕 5/1/12 3501 坦帕 5/6/12 快速參考卡:VLOOKUP 參照 使用 VLOOKUP |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |